package com.aboo.word2excel;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableCell;
import org.apache.poi.xwpf.usermodel.XWPFTableRow;
import org.junit.jupiter.api.Test;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

/**
 * @author louan
 * @date 2025-01-06
 * @description
 */
public class Word2ExcelTest01 {

    @Test
    public void test() {
        System.out.println("test");
    }

    @Test
    public void test01() throws IOException {
        String wordFilePath = "D:/workspace_data/JavaStudy20250106/24ZAS02P03D33-35532.docx";
        String excelFilePath = "D:/workspace_data/JavaStudy20250106/24ZAS02P03D33-35532.xlsx";

        // 将Word中的表格迁移到Excel
        transferWordTable2Excel(wordFilePath, excelFilePath);
        // 移除Excel中的空列
        removeExcelEmptyColumns(excelFilePath);

        mergeExcelCells(excelFilePath);
    }

    /**
     * @param wordFilePath
     * @param excelFilePath
     * @throws IOException
     * @description 将Word中的表格迁移到Excel
     */
    private void transferWordTable2Excel(String wordFilePath, String excelFilePath) throws IOException {
        try (FileInputStream fis = new FileInputStream(wordFilePath);
             XWPFDocument document = new XWPFDocument(fis);
             Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Red Text Tables");

            int rowIndex = 0;
            for (XWPFTable table : document.getTables()) {
                for (XWPFTableRow row : table.getRows()) {
                    boolean hasRedText = false;
                    for (XWPFTableCell cell : row.getTableCells()) {
                        for (XWPFParagraph paragraph : cell.getParagraphs()) {
                            for (XWPFRun run : paragraph.getRuns()) {
                                if (run.getColor() != null && run.getColor().equals("FF0000")) {
                                    hasRedText = true;
                                    break;
                                }
                            }
                        }
                    }
                    if (hasRedText) {
                        Row excelRow = sheet.createRow(rowIndex++);
                        int cellIndex = 0;
                        for (XWPFTableCell cell : row.getTableCells()) {
                            Cell excelCell = excelRow.createCell(cellIndex++);
                            String cellText = cell.getText();
                            excelCell.setCellValue(cell.getText());

                            // Handle merged cells
                            if (cell.getCTTc().isSetTcPr() && cell.getCTTc().getTcPr().isSetGridSpan()) {
                                int colspan = cell.getCTTc().getTcPr().getGridSpan().getVal().intValue();
                                for (int i = 1; i < colspan; i++) {
                                    excelCell = excelRow.createCell(cellIndex++);
                                    excelCell.setCellValue("");
                                }
                            }
                        }
                    }
                }
            }

            try (FileOutputStream fos = new FileOutputStream(excelFilePath)) {
                workbook.write(fos);
            }
        }
    }

    /**
     * @param excelFilePath
     * @throws IOException
     * @description 移除Excel中的空列
     */
    private static void removeExcelEmptyColumns(String excelFilePath) throws IOException {
        try (FileInputStream fis = new FileInputStream(excelFilePath);
             Workbook workbook = new XSSFWorkbook(fis)) {

            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                removeSheetEmptyColumns(sheet);
            }

            try (FileOutputStream fos = new FileOutputStream(excelFilePath)) {
                workbook.write(fos);
            }
        }
    }

    /**
     * @param sheet
     * @description 移除Sheet中的空列
     * @author louan
     */
    private static void removeSheetEmptyColumns(Sheet sheet) {
        int maxColumn = 0;
        for (Row row : sheet) {
            if (row.getLastCellNum() > maxColumn) {
                maxColumn = row.getLastCellNum();
            }
        }

        for (int col = maxColumn - 1; col >= 0; col--) {
            boolean isEmpty = true;
            for (Row row : sheet) {
                Cell cell = row.getCell(col);
                String cellText = cell.getStringCellValue();
                if (cell != null && cell.getCellType() != CellType.BLANK && !cell.getStringCellValue().isEmpty()) {
                    isEmpty = false;
                    break;
                }
            }
            if (isEmpty) {
                removeColumn(sheet, col);
            }
        }
    }

    /**
     * @param sheet
     * @param columnIndex
     * @description 删除Sheet中的指定列
     * @author louan
     */
    private static void removeColumn(Sheet sheet, int columnIndex) {
        for (Row row : sheet) {
            Cell cell = row.getCell(columnIndex);
            if (cell != null) {
                row.removeCell(cell);
            }
        }

        // Shift columns to the left
        for (Row row : sheet) {
            for (int col = columnIndex; col < row.getLastCellNum(); col++) {
                Cell oldCell = row.getCell(col + 1);
                Cell newCell = row.createCell(col, oldCell.getCellType());
                cloneCell(newCell, oldCell);
                row.removeCell(oldCell);
            }
        }
    }

    private static void cloneCell(Cell newCell, Cell oldCell) {
        if (oldCell == null) {
            newCell.setCellType(CellType.BLANK);
            return;
        }

        switch (oldCell.getCellType()) {
            case STRING:
                newCell.setCellValue(oldCell.getStringCellValue());
                break;
            case BOOLEAN:
                newCell.setCellValue(oldCell.getBooleanCellValue());
                break;
            case NUMERIC:
                newCell.setCellValue(oldCell.getNumericCellValue());
                break;
            case FORMULA:
                newCell.setCellFormula(oldCell.getCellFormula());
                break;
            case ERROR:
                newCell.setCellErrorValue(oldCell.getErrorCellValue());
                break;
            case BLANK:
                newCell.setCellType(CellType.BLANK);
                break;
        }
    }

    public static void mergeExcelCells(String excelFilePath) throws IOException {
        try (FileInputStream fis = new FileInputStream(excelFilePath);
             Workbook workbook = new XSSFWorkbook(fis)) {
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                mergeCells(sheet);
            }

            try (FileOutputStream fos = new FileOutputStream(excelFilePath)) {
                workbook.write(fos);
            }
        }
    }

    private static void mergeCells(Sheet sheet) {
        for (Row row : sheet) {
            for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                Cell cell = row.getCell(colIndex);
                String cellFirstText = cell.getStringCellValue();
                if (cell != null && cell.getCellType() != CellType.BLANK && !cell.getStringCellValue().isEmpty()) {
                    boolean allEmpty = true;
                    int lastEmptyColIndex = colIndex;
                    for (int nextColIndex = colIndex + 1; nextColIndex < row.getLastCellNum(); nextColIndex++) {
                        Cell nextCell = row.getCell(nextColIndex);
                        String cellSecondText = nextCell.getStringCellValue();
                        if (nextCell != null && nextCell.getCellType() != CellType.BLANK && !nextCell.getStringCellValue().isEmpty()) {
                            if (lastEmptyColIndex == (colIndex + 1)) {
                                allEmpty = false;
                            }
                            break;
                        }
                        lastEmptyColIndex = nextColIndex;
                    }
                    if (allEmpty && lastEmptyColIndex > colIndex) {
                        sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), colIndex, lastEmptyColIndex));
                    }
                }
            }
        }
    }
}
